-- DROP SCHEMA public;

CREATE SCHEMA public AUTHORIZATION pg_database_owner;

COMMENT ON SCHEMA public IS 'standard public schema';

-- DROP SEQUENCE public.cm_dept_seq;

CREATE SEQUENCE public.cm_dept_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 2
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.cm_file_seq;

CREATE SEQUENCE public.cm_file_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 2
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.cm_job_seq;

CREATE SEQUENCE public.cm_job_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 3
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.cm_menu_seq;

CREATE SEQUENCE public.cm_menu_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 54
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.cm_org_seq;

CREATE SEQUENCE public.cm_org_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 2
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.cm_post_seq;

CREATE SEQUENCE public.cm_post_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 2
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.cm_role_seq;

CREATE SEQUENCE public.cm_role_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 3
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.cm_user_seq;

CREATE SEQUENCE public.cm_user_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1010
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.gen_directory_seq;

CREATE SEQUENCE public.gen_directory_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.gen_table_column_seq;

CREATE SEQUENCE public.gen_table_column_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.gen_table_seq;

CREATE SEQUENCE public.gen_table_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.log_job_log_seq;

CREATE SEQUENCE public.log_job_log_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.log_oper_log_seq;

CREATE SEQUENCE public.log_oper_log_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 41
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.tr_account_id_seq;

CREATE SEQUENCE public.tr_account_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.tr_role_account_id_seq;

CREATE SEQUENCE public.tr_role_account_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.tr_source_id_seq;

CREATE SEQUENCE public.tr_source_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;-- public.cm_config definition

-- Drop table

-- DROP TABLE cm_config;

CREATE TABLE cm_config (
	id varchar(30) NOT NULL,
	"group" varchar(100) NULL,
	"name" varchar(100) NULL,
	value text NULL,
	editable int4 NULL,
	sort int4 NULL,
	remark text NULL,
	create_by int4 NULL,
	create_time timestamp NULL,
	modify_by int4 NULL,
	modify_time timestamp NULL,
	CONSTRAINT pk_cm_config PRIMARY KEY (id)
);


-- public.cm_dept definition

-- Drop table

-- DROP TABLE cm_dept;

CREATE TABLE cm_dept (
	id int4 NOT NULL DEFAULT nextval('cm_dept_seq'::regclass),
	p_id int4 NULL DEFAULT 0,
	"name" varchar(30) NULL,
	leader varchar(20) NULL,
	phone varchar(11) NULL,
	email varchar(50) NULL,
	state int4 NULL DEFAULT 0,
	is_del int4 NULL DEFAULT 0,
	sort int4 NULL DEFAULT 0,
	create_by int4 NULL,
	create_time timestamp NULL,
	modify_by int4 NULL,
	modify_time timestamp NULL,
	CONSTRAINT pk_cm_dept PRIMARY KEY (id)
);


-- public.cm_dept_user definition

-- Drop table

-- DROP TABLE cm_dept_user;

CREATE TABLE cm_dept_user (
	dept_id int4 NOT NULL,
	user_id int4 NOT NULL,
	CONSTRAINT pk_cm_dept_user PRIMARY KEY (dept_id, user_id)
);


-- public.cm_dict definition

-- Drop table

-- DROP TABLE cm_dict;

CREATE TABLE cm_dict (
	id varchar(30) NOT NULL,
	p_id varchar(30) NULL,
	"name" varchar(100) NOT NULL,
	value varchar(128) NULL,
	editable int4 NULL DEFAULT 0,
	state int4 NULL DEFAULT 0,
	sort int4 NULL,
	remark varchar(255) NULL,
	create_by int4 NULL,
	create_time timestamp NULL,
	modify_by int4 NULL,
	modify_time timestamp NULL,
	CONSTRAINT pk_cm_dict PRIMARY KEY (id)
);


-- public.cm_file definition

-- Drop table

-- DROP TABLE cm_file;

CREATE TABLE cm_file (
	id int8 NOT NULL DEFAULT nextval('cm_file_seq'::regclass),
	"path" varchar(200) NULL,
	image int4 NULL,
	used int4 NULL DEFAULT 0,
	create_by int4 NULL,
	create_time timestamp NULL,
	CONSTRAINT pk_cm_file PRIMARY KEY (id)
);


-- public.cm_job definition

-- Drop table

-- DROP TABLE cm_job;

CREATE TABLE cm_job (
	id int4 NOT NULL DEFAULT nextval('cm_job_seq'::regclass),
	"name" varchar(100) NULL,
	"group" varchar(100) NULL DEFAULT 'DEFAULT'::character varying,
	corn varchar(200) NULL,
	clazz varchar(100) NULL,
	state int4 NULL DEFAULT 0,
	param_json json NULL,
	"sql" text NULL,
	last_time timestamp NULL,
	create_by int4 NULL,
	create_time timestamp NULL,
	modidy_by int4 NULL,
	modify_time timestamp NULL,
	CONSTRAINT pk_cm_job PRIMARY KEY (id)
);


-- public.cm_menu definition

-- Drop table

-- DROP TABLE cm_menu;

CREATE TABLE cm_menu (
	id int4 NOT NULL DEFAULT nextval('cm_menu_seq'::regclass),
	p_id int4 NULL DEFAULT 0,
	icon varchar(100) NULL DEFAULT '#'::character varying,
	"name" varchar(50) NOT NULL,
	"type" int4 NULL,
	is_link int4 NULL DEFAULT 0,
	"path" varchar(200) NULL,
	component varchar(255) NULL,
	visible int4 NULL DEFAULT 0,
	controller varchar(100) NULL,
	"action" varchar(100) NULL,
	sort int4 NULL DEFAULT 0,
	remark varchar(500) NULL,
	create_by int4 NULL,
	create_time timestamp NULL,
	modify_by int4 NULL,
	modify_time timestamp NULL,
	CONSTRAINT pk_cm_menu PRIMARY KEY (id)
);


-- public.cm_org definition

-- Drop table

-- DROP TABLE cm_org;

CREATE TABLE cm_org (
	id int4 NOT NULL DEFAULT nextval('cm_org_seq'::regclass),
	p_id int4 NULL DEFAULT 0,
	ancestors varchar(200) NULL,
	"name" varchar(30) NULL,
	leader varchar(20) NULL,
	phone varchar(11) NULL,
	email varchar(50) NULL,
	state int4 NULL DEFAULT 0,
	is_del int4 NULL DEFAULT 0,
	sort int4 NULL DEFAULT 0,
	create_by int4 NULL,
	create_time timestamp NULL,
	modify_by int4 NULL,
	modify_time timestamp NULL,
	CONSTRAINT pk_cm_org PRIMARY KEY (id)
);


-- public.cm_org_user definition

-- Drop table

-- DROP TABLE cm_org_user;

CREATE TABLE cm_org_user (
	org_id int4 NOT NULL,
	user_id int4 NOT NULL,
	CONSTRAINT pk_cm_org_user PRIMARY KEY (org_id, user_id)
);


-- public.cm_post definition

-- Drop table

-- DROP TABLE cm_post;

CREATE TABLE cm_post (
	id int4 NOT NULL DEFAULT nextval('cm_post_seq'::regclass),
	code varchar(64) NOT NULL,
	"name" varchar(50) NOT NULL,
	sort int4 NOT NULL,
	state bpchar(1) NOT NULL,
	is_del int4 NULL,
	remark varchar(500) NULL,
	create_by int4 NULL,
	create_time timestamp NULL,
	modify_by int4 NULL,
	modify_time timestamp NULL,
	CONSTRAINT pk_cm_post PRIMARY KEY (id)
);


-- public.cm_role definition

-- Drop table

-- DROP TABLE cm_role;

CREATE TABLE cm_role (
	id int4 NOT NULL DEFAULT nextval('cm_role_seq'::regclass),
	"name" varchar(30) NOT NULL,
	sort int4 NOT NULL,
	"scope" int4 NULL DEFAULT 1,
	state int4 NOT NULL DEFAULT 0,
	is_del int4 NULL DEFAULT 0,
	remark varchar(500) NULL,
	create_by int4 NULL,
	create_time timestamp NOT NULL,
	modify_by int4 NULL,
	modify_time timestamp NULL,
	CONSTRAINT pk_cm_role PRIMARY KEY (id)
);


-- public.cm_role_dept definition

-- Drop table

-- DROP TABLE cm_role_dept;

CREATE TABLE cm_role_dept (
	role_id int4 NOT NULL,
	dept_id int4 NOT NULL,
	CONSTRAINT pk_cm_role_dept PRIMARY KEY (role_id, dept_id)
);


-- public.cm_role_menu definition

-- Drop table

-- DROP TABLE cm_role_menu;

CREATE TABLE cm_role_menu (
	role_id int4 NOT NULL,
	menu_id int4 NOT NULL,
	CONSTRAINT pk_cm_role_menu PRIMARY KEY (role_id, menu_id)
);


-- public.cm_role_org definition

-- Drop table

-- DROP TABLE cm_role_org;

CREATE TABLE cm_role_org (
	role_id int4 NOT NULL,
	org_id int4 NOT NULL,
	CONSTRAINT pk_cm_role_org PRIMARY KEY (role_id, org_id)
);


-- public.cm_role_user definition

-- Drop table

-- DROP TABLE cm_role_user;

CREATE TABLE cm_role_user (
	role_id int4 NOT NULL,
	user_id int4 NOT NULL,
	CONSTRAINT pk_cm_role_user PRIMARY KEY (role_id, user_id)
);


-- public.cm_session definition

-- Drop table

-- DROP TABLE cm_session;

CREATE TABLE cm_session (
	id varchar(40) NOT NULL,
	user_id int4 NOT NULL,
	expire_at int8 NOT NULL,
	CONSTRAINT pk_cm_session PRIMARY KEY (id)
);


-- public.cm_user definition

-- Drop table

-- DROP TABLE cm_user;

CREATE TABLE cm_user (
	id int4 NOT NULL DEFAULT nextval('cm_user_seq'::regclass),
	dept_id int4 NULL,
	org_id int4 NULL,
	user_type int4 NULL DEFAULT 1,
	account varchar(30) NOT NULL,
	nickname varchar(30) NOT NULL,
	email varchar(50) NULL,
	phone varchar(11) NULL,
	sex int4 NULL DEFAULT 0,
	avatar int4 NULL,
	salt varchar(100) NULL,
	"password" varchar(100) NULL,
	state int4 NULL DEFAULT 0,
	is_del int4 NULL DEFAULT 0,
	login_ip varchar(50) NULL,
	login_time timestamp NULL,
	remark varchar(500) NULL,
	create_by int4 NULL,
	create_time timestamp NULL,
	modify_by int4 NULL,
	modify_time timestamp NULL,
	CONSTRAINT pk_cm_user PRIMARY KEY (id)
);
CREATE UNIQUE INDEX cm_user_idx1 ON public.cm_user USING btree (user_type, account);


-- public.cm_user_post definition

-- Drop table

-- DROP TABLE cm_user_post;

CREATE TABLE cm_user_post (
	user_id int4 NOT NULL,
	post_id int4 NOT NULL,
	sort int4 NULL DEFAULT 0,
	CONSTRAINT pk_cm_user_post PRIMARY KEY (user_id, post_id)
);


-- public.gen_directory definition

-- Drop table

-- DROP TABLE gen_directory;

CREATE TABLE gen_directory (
	id int4 NOT NULL DEFAULT nextval('gen_directory_seq'::regclass),
	"path" varchar(10) NULL,
	"name" varchar(10) NULL,
	icon varchar(50) NULL,
	prefix varchar(50) NULL,
	is_del int4 NULL DEFAULT 0,
	create_by int4 NULL,
	create_time timestamp NULL,
	modify_by int4 NULL,
	modify_time timestamp NULL,
	CONSTRAINT pk_gen_directory PRIMARY KEY (id)
);


-- public.gen_table definition

-- Drop table

-- DROP TABLE gen_table;

CREATE TABLE gen_table (
	id int4 NOT NULL DEFAULT nextval('gen_table_seq'::regclass),
	"name" varchar(200) NULL,
	"comment" varchar(500) NULL,
	business varchar(200) NULL,
	"module" varchar(11) NULL,
	module_name varchar(50) NULL,
	module_icon varchar(50) NULL,
	pks varchar(100) NULL,
	model_pkg varchar(100) NULL,
	model_name varchar(100) NULL,
	srv_pkg varchar(100) NULL,
	srv_name varchar(30) NULL,
	ctr_pkg varchar(100) NULL,
	ctr_name varchar(50) NULL,
	ctr_key varchar(50) NULL,
	js_path varchar(50) NULL,
	vue_path varchar(50) NULL,
	author varchar(50) NULL,
	"options" varchar(1000) NULL,
	tpl varchar(200) NULL DEFAULT 'crud'::character varying,
	remark varchar(500) NULL,
	create_by int4 NULL,
	create_time timestamp NULL,
	modify_by int4 NULL,
	modify_time timestamp NULL,
	CONSTRAINT pk_gen_table PRIMARY KEY (id)
);


-- public.gen_table_column definition

-- Drop table

-- DROP TABLE gen_table_column;

CREATE TABLE gen_table_column (
	id int4 NOT NULL DEFAULT nextval('gen_table_column_seq'::regclass),
	table_id int4 NULL,
	"name" varchar(200) NULL,
	"comment" varchar(500) NULL,
	business varchar(200) NULL,
	"type" varchar(100) NULL,
	java_type varchar(500) NULL,
	is_pk int4 NULL,
	is_increment int4 NULL,
	is_required int4 NULL,
	is_insert int4 NULL,
	is_edit int4 NULL,
	is_list int4 NULL,
	is_query int4 NULL,
	query_type varchar(200) NULL DEFAULT '='::character varying,
	html_type varchar(200) NULL,
	dict_type varchar(200) NULL,
	sort int4 NULL,
	create_by int4 NULL,
	create_time timestamp NULL,
	modify_by int4 NULL,
	modify_time timestamp NULL,
	CONSTRAINT pk_gen_table_column PRIMARY KEY (id)
);


-- public.log_job_log definition

-- Drop table

-- DROP TABLE log_job_log;

CREATE TABLE log_job_log (
	id int4 NOT NULL DEFAULT nextval('log_job_log_seq'::regclass),
	"group" varchar(50) NULL,
	"name" varchar(50) NULL,
	start_time timestamp NULL,
	end_time timestamp NULL,
	res int4 NULL DEFAULT 0,
	message varchar(100) NULL,
	"exception" text NULL,
	CONSTRAINT pk_log_job_log PRIMARY KEY (id)
);


-- public.log_oper_log definition

-- Drop table

-- DROP TABLE log_oper_log;

CREATE TABLE log_oper_log (
	id int4 NOT NULL DEFAULT nextval('log_oper_log_seq'::regclass),
	"module" varchar(50) NULL,
	"name" varchar(50) NULL,
	"action" varchar(100) NULL,
	controller varchar(100) NULL,
	request_time timestamp NULL,
	request_ip varchar(30) NULL,
	request_header text NULL,
	request_parameter text NULL,
	response_time timestamp NULL,
	response_body text NULL,
	code int4 NULL,
	dispose int4 NULL,
	user_id int4 NULL,
	CONSTRAINT pk_log_oper_log PRIMARY KEY (id)
);


-- public."sequence" definition

-- Drop table

-- DROP TABLE "sequence";

CREATE TABLE "sequence" (
	"name" varchar(50) NOT NULL,
	current_value int4 NOT NULL,
	"increment" int4 NOT NULL DEFAULT 1,
	CONSTRAINT pk_sequence PRIMARY KEY (name)
);


-- public.tr_account definition

-- Drop table

-- DROP TABLE tr_account;

CREATE TABLE tr_account (
	id serial4 NOT NULL, -- 主键
	code varchar(32) NOT NULL, -- 账户编码
	org_id int8 NOT NULL, -- 主体
	source_id int4 NOT NULL, -- 服务器id
	account_type bpchar(1) NOT NULL DEFAULT '1'::bpchar, -- 账户类型
	account varchar(32) NOT NULL, -- 账户
	"name" varchar(32) NOT NULL, -- 账户名称
	json_data jsonb NOT NULL DEFAULT '{}'::jsonb, -- 扩展
	CONSTRAINT tr_account_code UNIQUE (code),
	CONSTRAINT tr_account_pkey PRIMARY KEY (id),
	CONSTRAINT tr_account_source_id_account_key UNIQUE (source_id, account)
);
COMMENT ON TABLE public.tr_account IS '交易账户';

-- Column comments

COMMENT ON COLUMN public.tr_account.id IS '主键';
COMMENT ON COLUMN public.tr_account.code IS '账户编码';
COMMENT ON COLUMN public.tr_account.org_id IS '主体';
COMMENT ON COLUMN public.tr_account.source_id IS '服务器id';
COMMENT ON COLUMN public.tr_account.account_type IS '账户类型';
COMMENT ON COLUMN public.tr_account.account IS '账户';
COMMENT ON COLUMN public.tr_account."name" IS '账户名称';
COMMENT ON COLUMN public.tr_account.json_data IS '扩展';


-- public.tr_account_user definition

-- Drop table

-- DROP TABLE tr_account_user;

CREATE TABLE tr_account_user (
	user_id int4 NOT NULL, -- 用户ID
	code varchar(32) NOT NULL, -- 账户编码
	CONSTRAINT tr_account_user_user_id_code_key UNIQUE (user_id, code)
);
COMMENT ON TABLE public.tr_account_user IS '用户产品查询权限';

-- Column comments

COMMENT ON COLUMN public.tr_account_user.user_id IS '用户ID';
COMMENT ON COLUMN public.tr_account_user.code IS '账户编码';


-- public.tr_commod definition

-- Drop table

-- DROP TABLE tr_commod;

CREATE TABLE tr_commod (
	code varchar(6) NOT NULL, -- 品种代码
	"name" varchar(20) NOT NULL, -- 名称
	is_cp bpchar(1) NOT NULL DEFAULT '0'::bpchar, -- 期权标志
	is_use bpchar(1) NOT NULL DEFAULT '1'::bpchar, -- 使用标志
	unit int4 NOT NULL, -- 单位
	props varchar(32) NULL, -- 板块
	mk_code varchar(6) NOT NULL, -- 交易所代码
	CONSTRAINT tr_commod_pkey PRIMARY KEY (code)
);
COMMENT ON TABLE public.tr_commod IS '交易品种';

-- Column comments

COMMENT ON COLUMN public.tr_commod.code IS '品种代码';
COMMENT ON COLUMN public.tr_commod."name" IS '名称';
COMMENT ON COLUMN public.tr_commod.is_cp IS '期权标志';
COMMENT ON COLUMN public.tr_commod.is_use IS '使用标志';
COMMENT ON COLUMN public.tr_commod.unit IS '单位';
COMMENT ON COLUMN public.tr_commod.props IS '板块';
COMMENT ON COLUMN public.tr_commod.mk_code IS '交易所代码';


-- public.tr_day_incomes definition

-- Drop table

-- DROP TABLE tr_day_incomes;

CREATE TABLE tr_day_incomes (
	account_id int4 NULL,
	tran_date varchar(10) NOT NULL,
	commod varchar(6) NULL,
	hold_income numeric(12, 2) NULL DEFAULT 0.00,
	close_income numeric(12, 2) NULL DEFAULT 0.00,
	volume numeric(12, 2) NOT NULL DEFAULT 0.00,
	in_value numeric(20, 2) NULL DEFAULT 0.00,
	in_hands int4 NULL DEFAULT 0,
	out_value numeric(20, 2) NULL DEFAULT 0.00,
	out_hands int4 NOT NULL DEFAULT 0,
	w_hands int4 NOT NULL DEFAULT 0,
	l_hands int4 NOT NULL DEFAULT 0,
	w_income numeric(12, 2) NOT NULL DEFAULT 0.00,
	l_income numeric(12, 2) NOT NULL DEFAULT 0.00,
	daily_income numeric(12, 2) NOT NULL DEFAULT 0.00,
	yes_income numeric(12, 2) NOT NULL DEFAULT 0.00,
	bail numeric(12, 2) NULL DEFAULT 0.00,
	fee numeric(12, 2) NULL DEFAULT 0.00
);
CREATE UNIQUE INDEX tt_day_incomes_idx1 ON public.tr_day_incomes USING btree (account_id, tran_date, commod);
CREATE INDEX tt_day_incomes_idx2 ON public.tr_day_incomes USING btree (account_id, tran_date);


-- public.tr_fund definition

-- Drop table

-- DROP TABLE tr_fund;

CREATE TABLE tr_fund (
	account_id int4 NULL, -- 交易账户主键
	tran_month bpchar(7) NULL, -- 交易月份
	json_data jsonb NOT NULL DEFAULT '{}'::jsonb, -- 扩展=>{"交易日":[期初结存,期末结存,保证金,平仓盈亏,持仓盯市盈亏,手续费,风险度,可用资金,入金,出金]}
	CONSTRAINT tr_fund_account_id_tran_month_key UNIQUE (account_id, tran_month)
);
COMMENT ON TABLE public.tr_fund IS '交易账户';

-- Column comments

COMMENT ON COLUMN public.tr_fund.account_id IS '交易账户主键';
COMMENT ON COLUMN public.tr_fund.tran_month IS '交易月份';
COMMENT ON COLUMN public.tr_fund.json_data IS '扩展=>{"交易日":[期初结存,期末结存,保证金,平仓盈亏,持仓盯市盈亏,手续费,风险度,可用资金,入金,出金]}';


-- public.tr_hold definition

-- Drop table

-- DROP TABLE tr_hold;

CREATE TABLE tr_hold (
	account_id int4 NULL, -- 交易账户主键
	tran_date bpchar(10) NULL, -- 交易日
	json_data jsonb NOT NULL DEFAULT '{}'::jsonb, -- 扩展=>[品种,合约,开仓日期,投保,买卖,持仓量,开仓价,昨结算价,今结算价,浮动盈亏,盯市盈亏,保证金]
	CONSTRAINT tr_hold_account_id_tran_date_key UNIQUE (account_id, tran_date)
);
COMMENT ON TABLE public.tr_hold IS '交易持仓流水';

-- Column comments

COMMENT ON COLUMN public.tr_hold.account_id IS '交易账户主键';
COMMENT ON COLUMN public.tr_hold.tran_date IS '交易日';
COMMENT ON COLUMN public.tr_hold.json_data IS '扩展=>[品种,合约,开仓日期,投保,买卖,持仓量,开仓价,昨结算价,今结算价,浮动盈亏,盯市盈亏,保证金]';


-- public.tr_close definition

-- Drop table

-- DROP TABLE tr_close;

CREATE TABLE tr_close (
	account_id int4 NULL, -- 交易账户主键
	tran_date bpchar(10) NULL, -- 交易日
	json_data jsonb NOT NULL DEFAULT '{}'::jsonb, -- 扩展=>[品种,合约,买卖,成交价,开仓价,手数,昨结算价,平仓盈亏,开仓日期,开仓价,开仓成交编号,逐笔平仓盈亏]
	CONSTRAINT tr_close_account_id_tran_date_key UNIQUE (account_id, tran_date)
);
COMMENT ON TABLE public.tr_close IS '交易平仓流水';

-- Column comments

COMMENT ON COLUMN public.tr_close.account_id IS '交易账户主键';
COMMENT ON COLUMN public.tr_close.tran_date IS '交易日';
COMMENT ON COLUMN public.tr_close.json_data IS '扩展=>[品种,合约,买卖,成交价,开仓价,手数,昨结算价,平仓盈亏,开仓日期,开仓价,开仓成交编号,逐笔平仓盈亏]';


-- public.tr_live_instrument definition

-- Drop table

-- DROP TABLE tr_live_instrument;

CREATE TABLE tr_live_instrument (
	instrument_id varchar(16) NOT NULL, -- 合约代码
	last_price numeric(18, 3) NULL DEFAULT NULL::numeric,
	pre_settle numeric(18, 3) NULL DEFAULT NULL::numeric,
	CONSTRAINT tr_live_instrument_pkey PRIMARY KEY (instrument_id)
);
COMMENT ON TABLE public.tr_live_instrument IS '实时合约价格';

-- Column comments

COMMENT ON COLUMN public.tr_live_instrument.instrument_id IS '合约代码';


-- public.tr_live_position definition

-- Drop table

-- DROP TABLE tr_live_position;

CREATE TABLE tr_live_position (
	code varchar(32) NULL, -- 账号代码：tr_account.code
	product_id varchar(6) NOT NULL,
	instrument_id varchar(16) NOT NULL,
	hedge_flag bpchar(1) NOT NULL DEFAULT '1'::bpchar,
	direction bpchar(1) NOT NULL DEFAULT '1'::bpchar,
	volume int4 NOT NULL DEFAULT 0,
	margin numeric(20, 2) NOT NULL DEFAULT 0.00,
	avg_openpricebyvol numeric(20, 2) NOT NULL DEFAULT 0.00,
	avg_openprice numeric(20, 2) NOT NULL DEFAULT 0.00,
	today_volume int4 NOT NULL DEFAULT 0,
	request_id int4 NOT NULL
);
COMMENT ON TABLE public.tr_live_position IS '实时持仓';

-- Column comments

COMMENT ON COLUMN public.tr_live_position.code IS '账号代码：tr_account.code';


-- public.tr_live_trade definition

-- Drop table

-- DROP TABLE tr_live_trade;

CREATE TABLE tr_live_trade (
	code varchar(32) NULL, -- 账号代码：tr_account.code
	instrument_id varchar(16) NOT NULL,
	offset_flag bpchar(1) NOT NULL,
	hedge_flag bpchar(1) NOT NULL,
	direction bpchar(1) NOT NULL,
	volume int4 NOT NULL,
	price numeric(12, 2) NOT NULL,
	trade_id varchar(16) NULL,
	order_sysid varchar(16) NULL,
	trade_date bpchar(8) NULL,
	trade_time bpchar(8) NULL,
	trade_type bpchar(1) NULL,
	trading_day bpchar(8) NULL
);
COMMENT ON TABLE public.tr_live_trade IS '实时每日成交';

-- Column comments

COMMENT ON COLUMN public.tr_live_trade.code IS '账号代码：tr_account.code';


-- public.tr_org_server definition

-- Drop table

-- DROP TABLE tr_org_server;

CREATE TABLE tr_org_server (
	org_id int4 NOT NULL, -- 机构ID
	server_id int4 NOT NULL, -- 服务器ID
	CONSTRAINT tr_org_server_pkey PRIMARY KEY (org_id, server_id)
);
COMMENT ON TABLE public.tr_org_server IS '机构服务器表';

-- Column comments

COMMENT ON COLUMN public.tr_org_server.org_id IS '机构ID';
COMMENT ON COLUMN public.tr_org_server.server_id IS '服务器ID';


-- public.tr_role_account definition

-- Drop table

-- DROP TABLE tr_role_account;

CREATE TABLE tr_role_account (
	id serial4 NOT NULL, -- 权限主键
	"name" varchar(64) NOT NULL, -- 权限名
	org_id int4 NOT NULL, -- 机构ID
	code_array _varchar NULL, -- 账户代码
	CONSTRAINT tr_role_account_pkey PRIMARY KEY (id)
);
COMMENT ON TABLE public.tr_role_account IS '产品权限表';

-- Column comments

COMMENT ON COLUMN public.tr_role_account.id IS '权限主键';
COMMENT ON COLUMN public.tr_role_account."name" IS '权限名';
COMMENT ON COLUMN public.tr_role_account.org_id IS '机构ID';
COMMENT ON COLUMN public.tr_role_account.code_array IS '账户代码';


-- public.tr_source definition

-- Drop table

-- DROP TABLE tr_source;

CREATE TABLE tr_source (
	id serial4 NOT NULL, -- 主键
	org_id int8 NOT NULL, -- 主体
	server_id int4 NOT NULL, -- 后台端口
	userid varchar(32) NOT NULL, -- 统一账户
	"password" varchar(64) NOT NULL, -- 统一密码
	json_web jsonb NOT NULL DEFAULT '{}'::jsonb,
	json_risk jsonb NOT NULL DEFAULT '{}'::jsonb,
	CONSTRAINT tr_source_pkey PRIMARY KEY (id)
);
COMMENT ON TABLE public.tr_source IS '交易日历';

-- Column comments

COMMENT ON COLUMN public.tr_source.id IS '主键';
COMMENT ON COLUMN public.tr_source.org_id IS '主体';
COMMENT ON COLUMN public.tr_source.server_id IS '后台端口';
COMMENT ON COLUMN public.tr_source.userid IS '统一账户';
COMMENT ON COLUMN public.tr_source."password" IS '统一密码';


-- public.tr_trade definition

-- Drop table

-- DROP TABLE tr_trade;

CREATE TABLE tr_trade (
	account_id int4 NULL, -- 交易账户主键
	tran_date bpchar(10) NULL, -- 交易日
	json_data jsonb NOT NULL DEFAULT '{}'::jsonb, -- 扩展=>[品种,合约,买卖,投保,成交价,手数,成交额,开平,手续费,平仓盈亏,成交日期,成交序号]
	CONSTRAINT tr_trade_account_id_tran_date_key UNIQUE (account_id, tran_date)
);
COMMENT ON TABLE public.tr_trade IS '交易成交流水';

-- Column comments

COMMENT ON COLUMN public.tr_trade.account_id IS '交易账户主键';
COMMENT ON COLUMN public.tr_trade.tran_date IS '交易日';
COMMENT ON COLUMN public.tr_trade.json_data IS '扩展=>[品种,合约,买卖,投保,成交价,手数,成交额,开平,手续费,平仓盈亏,成交日期,成交序号]';


-- public.tr_user_account definition

-- Drop table

-- DROP TABLE tr_user_account;

CREATE TABLE tr_user_account (
	user_id int4 NOT NULL, -- 用户ID
	role_id int4 NOT NULL, -- 权限ID
	CONSTRAINT tr_user_account_pkey PRIMARY KEY (user_id, role_id)
);
COMMENT ON TABLE public.tr_user_account IS '用户交易账户表';

-- Column comments

COMMENT ON COLUMN public.tr_user_account.user_id IS '用户ID';
COMMENT ON COLUMN public.tr_user_account.role_id IS '权限ID';


-- public.tr_workday definition

-- Drop table

-- DROP TABLE tr_workday;

CREATE TABLE tr_workday (
	id varchar(4) NOT NULL,
	day_array _date NULL,
	CONSTRAINT tr_workday_pkey PRIMARY KEY (id)
);


-- public.vw_server source

CREATE OR REPLACE VIEW public.vw_server
AS SELECT x.id,
    t.org_id,
    x.server_ip,
    x.server_desc,
    x.web_protocol,
    x.web_port,
    x.uri_bill,
    x.risk_port
   FROM tr_org_server t,
    cm_config a,
    LATERAL jsonb_to_recordset(a.value::jsonb) x(id integer, server_ip character varying, server_desc character varying, web_protocol character varying, uri_bill character varying, web_port integer, risk_port integer)
  WHERE t.server_id = x.id AND a.id::text = 'rohon_server_config'::text;


-- public.vw_fund source

CREATE OR REPLACE VIEW public.vw_fund
AS SELECT a.account_id,
    a.tran_date,
    (a.data_array -> 0)::numeric AS presettleposit,			-- 上日结存
    (a.data_array -> 1)::numeric AS settleposit,				-- 当日结存
    (a.data_array -> 2)::numeric AS margin,					-- 保证金
    (a.data_array -> 3)::numeric AS closeprofit,				-- 平盈
    (a.data_array -> 4)::numeric AS positionprofit,			-- 持盈
    (a.data_array -> 5)::numeric AS commission,				-- 手续费
    (a.data_array -> 6)::numeric AS riskrate,					-- 仓位
    (a.data_array -> 7)::numeric AS availablemoney,		-- 可用资金
    (a.data_array -> 8)::numeric AS deposit,					-- 日入金
    (a.data_array -> 9)::numeric AS withdraw					-- 日出金
   FROM ( SELECT tr_fund.account_id,
            (jsonb_each(tr_fund.json_data)).key AS tran_date,
            (jsonb_each(tr_fund.json_data)).value AS data_array
           FROM tr_fund) a;


-- public.vw_trade source
--drop view public.vw_trade;
CREATE OR REPLACE VIEW public.vw_trade
AS SELECT a.account_id,
    a.tindex,
    a.tran_date AS tran_date,
    (a.data_array ->> 0)::character varying AS commod,				-- 品种码
    (a.data_array ->> 1)::character varying AS instrumentid,		-- 合约码
    (a.data_array ->> 2)::character varying AS direction,				-- 方向：买、卖
    (a.data_array ->> 3)::character varying AS hedgeflag,			-- 投套：投机、套保
    (a.data_array ->> 4)::numeric AS tradeprice,					-- 成交价格
    (a.data_array ->> 5)::numeric AS volume,						-- 手数
    (a.data_array ->> 6)::numeric AS totalcost,					-- 成交额
    (a.data_array ->> 7)::character varying AS offsetflag,	-- 开平：开仓、平仓
    (a.data_array ->> 8)::numeric AS commission,				-- 手续费
    (a.data_array ->> 9)::numeric AS closeprofit,					-- 平盈
    (a.data_array ->> 10)::date AS tradedate,						-- 成交日期
    (a.data_array ->> 11)::time without time zone AS tradetime,	-- 成交时间
    (a.data_array ->> 12)::integer AS tradeid								-- 成交序号
   FROM ( SELECT tr_trade.account_id,
            tr_trade.tran_date,
            generate_series(0, jsonb_array_length(tr_trade.json_data) - 1) AS tindex,
            jsonb_array_elements(tr_trade.json_data) AS data_array
           FROM tr_trade) a;


-- public.vw_hold source

CREATE OR REPLACE VIEW public.vw_hold
AS SELECT a.account_id,
    a.tran_date,
    (a.data_array ->> 0)::character varying AS commod,			-- 品种码
    (a.data_array ->> 1)::character varying AS instrumentid,	-- 合约码
    (a.data_array ->> 2)::character varying AS opendate,		-- 开仓日期
    (a.data_array ->> 3)::character varying AS hedgeflag,		-- 投套：投机、套保
    (a.data_array ->> 4)::character varying AS direction,			-- 方向：买、卖
    (a.data_array ->> 5)::numeric AS volume,				-- 手数
    (a.data_array ->> 6)::numeric AS openprice,			-- 开仓价格
    (a.data_array ->> 7)::numeric AS presettleprice,	-- 昨结价
    (a.data_array ->> 8)::numeric AS settleprice,			-- 结算价
    (a.data_array ->> 9)::numeric AS tradeprofit,			-- 持仓盈亏
    (a.data_array ->> 10)::numeric AS marketvalue,	-- 市值
    (a.data_array ->> 11)::numeric AS margin				-- 保证金
   FROM ( SELECT tr_hold.account_id,
            tr_hold.tran_date,
            jsonb_array_elements(tr_hold.json_data) AS data_array
           FROM tr_hold) a;


-- public.vw_day_statis source
          

CREATE OR REPLACE VIEW public.vw_day_statis
AS SELECT a.account_id,
    a.tran_date,
    (a.data_array ->> 0)::character varying AS commod,
    (a.data_array ->> 1)::numeric AS hold_income,
    (a.data_array ->> 2)::numeric AS hold_value,
    (a.data_array ->> 3)::numeric AS close_income,
    (a.data_array ->> 4)::numeric AS volume,
    (a.data_array ->> 5)::numeric AS in_value,
    (a.data_array ->> 6)::numeric AS in_hands,
    (a.data_array ->> 7)::numeric AS out_value,
    (a.data_array ->> 8)::numeric AS out_hands,
    (a.data_array ->> 9)::numeric AS w_hands,
    (a.data_array ->> 10)::numeric AS l_hands,
    (a.data_array ->> 11)::numeric AS w_income,
    (a.data_array ->> 12)::numeric AS l_income,
    (a.data_array ->> 13)::numeric AS bail,
    (a.data_array ->> 14)::numeric AS fee,
    (a.data_array ->> 15)::numeric AS w_income1
   FROM ( SELECT tr_day_statis.account_id,
            tr_day_statis.tran_date,
            jsonb_array_elements(tr_day_statis.json_data) AS data_array
           FROM tr_day_statis) a;
          
-- public.vw_workday source

CREATE OR REPLACE VIEW public.vw_workday
AS SELECT t.tran_date
   FROM ( SELECT unnest(tr_workday.day_array)::character varying AS tran_date
           FROM tr_workday) t;


create or replace view vw_account as
SELECT ta.id, ta.code, ta.account, ta.name, ta.source_id, ta.org_id, ta.account_type, jsonObj.*
FROM tr_account ta,
jsonb_to_record(ta.json_data) as jsonObj(
open_date varchar,
trans_begin varchar,
trans_end varchar,
back_max_date varchar,
margin decimal,
nav_max decimal,
nav_min decimal,
back_max decimal,
fund_net decimal,
nav_last decimal,
vol_last decimal,
accountId decimal,
nav_ratio decimal,
begin_fund decimal,
commission decimal,
sort_ratio decimal,
closeprofit decimal,
fund_profit decimal,
settleposit decimal,
sharpe_ratio decimal,
positionprofit decimal,
win_profit_rate decimal/*,nav_list decimal(8,4)[]*/);

CREATE OR REPLACE PROCEDURE public.convert_csv_to_integers(IN input_string text)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    input_array text[];
    i integer;
BEGIN
    input_array := string_to_array(input_string, ',');  -- 将逗号拼接的字符串分割成数组

    FOR i IN 1..array_length(input_array, 1) LOOP
        RAISE INFO '%：%', i, input_array[i];
    END LOOP;

END;
$procedure$
;

CREATE OR REPLACE FUNCTION public.find_in_set(str_to_find anyelement, str_list text)
 RETURNS boolean
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
DECLARE
    position integer:=0;
BEGIN
    -- 在str_list中查找str_to_find，str_list是以逗号分隔的字符串列表
    position := position(str_to_find || ',' IN str_list || ',');
    -- 如果找到，返回在列表中的位置（从1开始）
    -- 如果没找到，返回0
    RETURN position > 0;
END;
$function$
;

CREATE OR REPLACE PROCEDURE public.p_networth_calculation(IN accounts_split text)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
	rec_account record;
	rec_fund record;
	rec_sumary record;
	text_exception text;
	tranDate_ary varchar(10)[] := '{}'; -- 交易日数组
	vol_ary numeric(20,2)[] := '{}'; -- 日份额数组 
	nav_ary numeric(8,4)[] := '{}'; -- 日净值数组
	vol_pre numeric(20,2) := 0; -- 上日份额
	nav_pre numeric(8,4) := 1; -- 上日净值
	sums jsonb :='{}';
BEGIN
	for rec_account in 
		select id
		, coalesce((json_data->>'nav_last')::numeric,1) nav_last
		, coalesce((json_data->>'nav_min')::numeric,1) nav_min
		, coalesce((json_data->>'nav_max')::numeric,1) nav_max
		, coalesce((json_data->>'vol_last')::numeric,0) vol_last
		, coalesce((json_data->>'trans_begin'),'') trans_begin
		, coalesce((json_data->>'trans_end'),'') trans_end
		, coalesce((json_data->>'fund_net')::numeric,0) fund_net
		, coalesce((json_data->>'fund_profit')::numeric,0) fund_profit
		, coalesce((json_data->>'begin_fund')::numeric,0) begin_fund
		, coalesce((json_data->>'back_max')::numeric,0) back_max
		, coalesce((json_data->>'back_max_date'),'') back_max_date
		, coalesce(json_data->'nav_list','[]') nav_list
		from tr_account where id in(select (unnest(string_to_array(accounts_split, ',')))::int4)
		order by id
	loop
	  tranDate_ary:='{}';
	  vol_ary:='{}';
	  nav_ary:='{}';
	  vol_pre:=rec_account.vol_last;
	  nav_pre:=rec_account.nav_last;
	  --RAISE INFO '开始处理 %' , rec_account;
	  FOR rec_fund IN 
	  	SELECT t.tran_date,t.settleposit,t.presettleposit,t.deposit,t.withdraw,t.closeprofit,t.positionprofit,t.commission,t.margin
		 , 0::numeric(20,2) as vol, 0::numeric(8,4) as nav
		 from vw_fund t 
		 where t.account_id=rec_account.id 
		 and tran_date>COALESCE(rec_account.trans_end,'')
		 order by t.tran_date -- 假设有一个id列用于指定行的顺序

	  LOOP
	  	
		IF jsonb_array_length(rec_account.nav_list)=0 then -- 是新导入的账单
			rec_account.trans_begin=rec_fund.tran_date;
			rec_account.fund_net:=0;
			rec_account.fund_profit:=0;
			vol_pre := case when rec_fund.presettleposit>0 then rec_fund.presettleposit else 0 end;
			rec_account.begin_fund:= case 
				when rec_fund.presettleposit=0 then 
					case when rec_fund.deposit-rec_fund.withdraw=0 then rec_fund.settleposit else rec_fund.deposit-rec_fund.withdraw end
				else rec_fund.presettleposit end;
		END IF;
		rec_fund.vol := vol_pre+((rec_fund.deposit-rec_fund.withdraw)/nav_pre);
		rec_account.fund_net:=rec_account.fund_net+rec_fund.deposit-rec_fund.withdraw;
		rec_account.fund_profit:=rec_account.fund_profit+rec_fund.closeprofit+rec_fund.positionprofit-rec_fund.commission;
		
		--RAISE INFO '%-%-%' , rec_fund.tran_date,rec_fund.vol,nav_pre;
		rec_fund.nav := case when rec_fund.vol=0 or round(rec_fund.settleposit/rec_fund.vol,4)=0 then nav_pre else round(rec_fund.settleposit/rec_fund.vol,4) end;
		-- 考虑到一些离场后还有一些小值权益
		rec_fund.nav := case when rec_fund.nav<0.45 then 1 else rec_fund.nav end;
		rec_account.nav_min=least(rec_account.nav_min, rec_fund.nav);
		rec_account.nav_max=greatest(rec_account.nav_max, rec_fund.nav);
		IF rec_account.back_max<round((rec_account.nav_max-rec_fund.nav)/rec_account.nav_max,4) then
			rec_account.back_max=round((rec_account.nav_max-rec_fund.nav)/rec_account.nav_max,4);
			rec_account.back_max_date=rec_fund.tran_date;
		END IF;
		
		vol_pre :=rec_fund.vol;
		nav_pre :=rec_fund.nav;
		tranDate_ary := tranDate_ary||rec_fund.tran_date;
		vol_ary := vol_ary||rec_fund.vol;
		rec_account.nav_list:=jsonb_insert(rec_account.nav_list, '{-1}', to_jsonb(rec_fund.nav), true);
		-- RETURN QUERY SELECT vol_ary, nav_ary; -- 返回数组
		-- RAISE INFO '%【% ~ %】' , rec_fund.tran_date, rec_fund.vol, rec_fund.nav;
	  END LOOP;
-- 	  RAISE INFO '%-%' , to_jsonb(tranDate_ary),to_jsonb(nav_ary);
-- 	  RAISE INFO '%' , nav_ary;
	  --/*
	  if array_length(tranDate_ary,1)>0 then
-- 	  	RAISE INFO 'days:%' , tranDate_ary;
-- 		RAISE INFO 'navs:%' , rec_account.nav_list;
	  	-- 计算其它字段
		select
-- 		round(stddev(nav_grows),4) nav_std
-- 		,round((POW(1+AVG(nav_grows),250)-1-0.03)/stddev(nav_grows)/POW(250,0.5),2) sharpe_ratio
-- 		,round((POW(1+AVG(nav_grows),250)-1-0.03)/stddev(case when nav_grows<0 then nav_grows else 0 end)/POW(250,0.5),2) sort_ratio
-- 		,round(SUM(case when nav_grows<0 then 0 else 1 end)/COUNT(id)::numeric*ABS((SUM(case when nav_grows<0 then 0 else nav_grows end)/SUM(case when nav_grows<0 then 0 else 1 end))/(SUM(case when nav_grows<0 then nav_grows else 0 end)/SUM(case when nav_grows<0 then 1 else 0 end))),2) win_profit_rate
		AVG(nav_grows) growAvg
		,STDDEV(nav_grows) growStd
		,STDDEV(case when nav_grows<0 then nav_grows else 0 end) loseStd
		,COUNT(id) totals
		,SUM(case when nav_grows<0 then 0 else 1 end) winCnts
		,SUM(case when nav_grows<0 then 0 else nav_grows end) wins
		,SUM(case when nav_grows<0 then nav_grows else 0 end) loses
		,0 as win_profit_rate
		,-1 as sharpe_ratio
		,-1 as sort_ratio
		into rec_sumary
		from(
			select id,nav,coalesce(round((nav-lag(nav) over(order by tran_date))/(lag(nav) over(order by tran_date)),4),0) as nav_grows
			from (select rec_account.id, unnest(tranDate_ary) as tran_date, jsonb_array_elements(rec_account.nav_list)::numeric as nav) a
		) t group by id;
		
-- 		RAISE INFO '%' , to_json(rec_sumary);
		rec_sumary.growStd=round(rec_sumary.growStd,4);
-- 		rec_sumary.win_profit_rate=round(rec_sumary.winCnts/rec_sumary.totals*ABS((rec_sumary.wins/rec_sumary.winCnts)/(rec_sumary.loses/(rec_sumary.totals-rec_sumary.winCnts))), 2);
		-- 防止除零判断
		if rec_sumary.wins=0 then rec_sumary.win_profit_rate=0;	end if;
		if rec_sumary.loses=0 then rec_sumary.win_profit_rate=100;	end if;
		if rec_sumary.growStd!=0 and rec_sumary.loseStd!=0 then
			rec_sumary.sharpe_ratio=round((POW(1+rec_sumary.growAvg,250)-1-0.03)/rec_sumary.growStd/POW(250,0.5),2);
			rec_sumary.sort_ratio=round((POW(1+rec_sumary.growAvg,250)-1-0.03)/rec_sumary.loseStd/POW(250,0.5),2);
		end if;
		--RAISE INFO '开始处理 %' , rec_account.id;
		 update tr_account 
		 set json_data=json_data||jsonb_build_object(
		    'trans_begin', rec_account.trans_begin
		  , 'trans_end', rec_fund.tran_date
		  , 'settleposit', rec_fund.settleposit
	, 'closeprofit', rec_fund.closeprofit
	, 'positionprofit', rec_fund.positionprofit
	, 'commission', rec_fund.commission
	, 'margin', rec_fund.margin
		  , 'begin_fund', rec_account.begin_fund
		  , 'fund_net', rec_account.fund_net
		  , 'fund_profit', rec_account.fund_profit
		  , 'back_max', rec_account.back_max
		  , 'back_max_date', rec_account.back_max_date
		  , 'nav_last', rec_fund.nav
		  , 'nav_min', rec_account.nav_min
		  , 'nav_max', rec_account.nav_max
		  , 'vol_last', rec_fund.vol
		  , 'nav_ratio', rec_sumary.growStd
		  , 'sharpe_ratio', rec_sumary.sharpe_ratio
		  , 'sort_ratio', rec_sumary.sort_ratio
		  , 'win_profit_rate', rec_sumary.win_profit_rate
		  , 'nav_list', rec_account.nav_list
		 ) where id=rec_account.id;
	  end if;
	END LOOP;
END $procedure$
;